大家好！

昨晚發文被機器人提醒後，才發現即將完賽了；雖然迄今文章的結構、內容仍有許多待加強之處，但這30 天來因為有團隊成員的勉勵和制約，讓自己再忙再累都還是會硬著頭皮研究及練習想瞭解的知識，回頭看了鐵人賽的簡介，才好像又多明白了些什麼......

言歸正傳，最後一天想跟大家介紹鐵人賽期間的SQL GUI(Graphical User Interface) 利器——TablePlus：

之前曾用過pgAdmin、DBeaver，但使用上略不直覺；相較之下TablePlus 不僅使用直覺，且設定、連線資料庫容易，如官網的設定畫面參考：

按說明設定好欲連線的資料庫與其使用者的帳號密碼（可在config/database.yml 查看）等資訊後，即可以該GUI 查閱資料庫（若大大們是用Ubuntu 等vm，可能需要另外設定些資訊，這邊先不贅述）：

各tab 的粗略功能介紹：

1. Items：可觀看資料庫內的各資料表，下方按右鍵能操作刪除、新增、匯入資料表等功能，右側則可搭配搜尋（command + F）、篩選、排序等方式檢視各欄列entity
2. Queries：能利用folder 管理自己的SQL query 檔案，除了可將各專案的code 複製至此測試撈取的結果集外，這些query 檔支援跨資料庫能共用，所以在開發自己專案時可方便參考其他資料庫和其query statement 等，簡單說就是方便抄寫吧？ XD
3. History：可快速回顧過去用過的query，只可惜免費仔最多同時開兩個query、同個資料庫最多也只能開兩個connections，還是有人要跟魯魯一起團購？（但同時連線數似乎依OS 有別）

而基本的使用方式是new 一個query，並在裡面測試、組合自己的SQL query，如：

SELECT "userId", Count(completed)
    FROM todos 
    WHERE completed = TRUE
    GROUP BY completed, "userId"

接著按快捷鍵command + enter來執行query：

確定沒問題後，即可嘗試改寫成WITH query：

WITH todo_count AS
( 
	SELECT "userId", Count(completed)
    FROM todos 
    WHERE completed = TRUE
    GROUP BY completed, "userId"
)
SELECT *
FROM todo_count

從畫面可看到上方註解區塊是我第一次的query，接著才寫出第二次的WITH query 來確定結果集是對的，以便後續再CTE chain 下去；

而除了註解掉前面測試的區塊或搭配Histroy 使用外，也可主動選取某區塊執行：

如此便不需要一直toogle 註解（command + /）了！

該軟體雖然不支援command + +/-來zoom in/out，但可在prefernces 調整字體大小，適合需要多保護眼睛的你和我：

再來想分享在公司學到的妙招！

相信各位夥伴的公司應該會將龐大的SQL query 拆成小小的區塊，也許是用subquery 或用CTE，甚至搭配ActiveRecord 的query 方法及Ruby interpolation，如此便能更有效地運用ActvieRecord，可這也造成了一個問題，技術力不夠的話，可能跟被翻爛的書一樣，不知道怎麼在TablePlus 測試，如：

1. 先確定SQL query 無誤：

WITH posts_with_comments AS
	(
		SELECT *
		FROM "posts" ps
		LEFT JOIN "comments" cs ON cs."postId" = ps."id"
	),
	users_existed AS
	(
		SELECT *
		FROM posts_with_comments pwc
		LEFT JOIN users us ON us.id = pwc."userId"
		WHERE us.id IS NOT NULL
	)
SELECT *
FROM users_existed

接著可能會在專案的Class 或Module 的方法內這麼用：

Post.with(
  posts_with_comments: %[
                SELECT *
		FROM "posts" ps
		LEFT JOIN "comments" cs ON cs."postId" = ps."id"
  ]).with(
  users_existed: %[
		SELECT *
		FROM posts_with_comments pwc
		LEFT JOIN users us ON us.id = pwc."userId"
		WHERE us.id IS NOT NULL # 等會會試著將這condition 抽出來
  ])

接著我們試著將us.id IS NOT NULL抽出為一個獨立的判斷變數，如：

# I'm here
users_existed_cond = "userId".present? ? '1=1' : 'NULL'

Post.with(
  posts_with_comments: %[
                SELECT *
		FROM "posts" ps
		LEFT JOIN "comments" cs ON cs."postId" = ps."id"
  ]).with(
  users_existed: %[
		SELECT *
		FROM posts_with_comments pwc
		LEFT JOIN users us ON us.id = pwc."userId"
        WHERE #{users_existed_cond}
  ])

目前為止都很順利，可問題來了，當我們改寫成這樣，要怎麼在TablePlus 查看每個CTE 是否有誤呢？

這樣嗎？

應該不用附上結果了 XD

那該怎麼做呢？

.
.
.

方法1
訣竅是將上面的SQL query 加上之前用過的方法to_sql，再稍微整理一下，如：

irb(main):001:0> users_existed_cond = "userId".present? ? '1=1' : 'NULL'
"1=1"
irb(main):002:0> Post.with(
  posts_with_comments: %[
                SELECT *
                FROM "posts" ps
                LEFT JOIN "comments" cs ON cs."postId" = ps."id"
  ]).with(
  users_existed: %[
                SELECT *
                FROM posts_with_comments pwc
                LEFT JOIN users us ON us.id = pwc."userId"
        WHERE #{users_existed_cond}
  ]).to_sql
  
"WITH \"posts_with_comments\" AS (\n                SELECT *\n\t\tFROM \"posts\" ps\n\t\tLEFT JOIN \"comments\" cs ON cs.\"postId\" = ps.\"id\"\n  ), \"users_existed\" AS (\n\t\tSELECT *\n\t\tFROM posts_with_comments pwc\n\t\tLEFT JOIN users us ON us.id = pwc.\"userId\"\n        WHERE 1=1\n  ) SELECT \"posts\".* FROM \"posts\""

接著把這段稍微整理一下，如把跳脫、斷行字元都刪掉：

"WITH \"posts_with_comments\" AS (\n                SELECT *\n\t\tFROM \"posts\" ps\n\t\tLEFT JOIN \"comments\" cs ON cs.\"postId\" = ps.\"id\"\n  ), \"users_existed\" AS (\n\t\tSELECT *\n\t\tFROM posts_with_comments pwc\n\t\tLEFT JOIN users us ON us.id = pwc.\"userId\"\n        WHERE 1=1\n  ) SELECT \"posts\".* FROM \"posts\""

to

-- 可看到其實跟上段寫的raw SQL 差不多
WITH "posts_with_comments" AS 
	( 
		SELECT * FROM "posts" ps
		JOIN "comments" cs ON cs."postId" = ps."id")
	, 
"users_existed" AS 
	(
		SELECT * FROM posts_with_comments pwc
		LEFT JOIN users us ON us.id = pwc."userId"
		WHERE 1=1
	)
SELECT * FROM posts_with_comments

結果就會... 燈楞！！

若syntax 無誤，但結果集卻非自己預期的，則可開始一段段執行以debug，就能知道是從哪開始出問題的了！

方法2
assign 一個variable 給這段sql，就不用自己整理了XD，如：

users_existed_cond = "userId".present? ? '1=1' : 'NULL'

sql = Post.with(
  posts_with_comments: %[
                SELECT *
                FROM "posts" ps
                LEFT JOIN "comments" cs ON cs."postId" = ps."id"
  ]).with(
  users_existed: %[
                SELECT *
                FROM posts_with_comments pwc
                LEFT JOIN users us ON us.id = pwc."userId"
        WHERE #{users_existed_cond}
  ]).to_sql
  
posts = Post.find_by_sql(sql)

# 將console 的畫面滑到上方，可以看到這個區塊，就能看到SQL 的query 語法，接著丟進Table Plus、並一段一段檢查CTE，就能知道是哪裡出問題囉！

irb(main):016:0> posts = Post.find_by_sql(sql)
  Post Load (18.5ms)  WITH "posts_with_comments" AS (
                SELECT *
                FROM "posts" ps
                LEFT JOIN "comments" cs ON cs."postId" = ps."id"
  ), "users_existed" AS (
                SELECT *
                FROM posts_with_comments pwc
                LEFT JOIN users us ON us.id = pwc."userId"
        WHERE 1=1
  ) SELECT "posts".* FROM "posts"

當然，還是要稍微修改一下的啊：

如此就跟方法1 是一樣、但更快速的方法囉！

以上就是ActiveRecord query method + raw SQL + Ruby interpolation plus TablePlus 的搭配應用啦！

（但其實我的condition 寫錯了，所以得到的結果跟我預期的不同，但大概就是這樣用囉...）

以上，是被翻爛的書Day 30 的練習內容，那就到此結束啦！

補充:要使用Cte 前要記得先裝gem 喲：activerecord-cte

最後想不免俗地回顧這30 天來的心得，真的就如簡介提到的——原來我也做得到！

雖然自認有非常大的改善空間，尤其過去30 天因為剛好發生了不少事，除了平日晚上有開賽前早已安排的行程外（一堆聽起來就是藉口的私事），因而經常連假日整天都被佔用；如此在行程過滿的狀況下，每週往往都有連續工作了7 天的感覺，遺憾的是自己做事也不夠有效率；不僅沒妥善應用零碎時間，且即使到哪都帶著電腦，但產出低落又沒提前準備庫存，導致原先練習與研究的理想和成果因而不符預期，也3-4 次濫用了先發文才補的劣招；心裡時常會納悶著這些行程的priority 能否有更好的選擇，難道全都無法放手嗎？

但所幸在公司前輩的督促及鼓勵下，自己還是完賽了；因此Day 30 雖然只是個數字，然而自己還是有所收穫——如適逢公司剛好指派了refactor CTE 的任務，因為剛好研究了model 關聯與練習了些SQL query，某天意外發現、自己已不再像過去一樣，需要看一句抄一句，而能達到最低標的no syntax error

因此自己心裡期許這樣的學習模式與堅持都能持續進行，讓自己的能力可穩定（緩慢）地成長，畢竟若只靠公司前輩的善意安排，即便抄寫做出來了，還是有許多技術債無法在白天上班時靜心補足，也會時常有囫圇吞棗或不知其所以然的遺憾

...

總之就這樣啦！再次感謝蜉蝣生物的團長 & 團員們，沒想到我們真的一起完成了這項任務、感謝同事大大們在旁觀察並適時給予建言與協助、感謝家人和朋友在這段時間相處時忍受我突然拿出電腦、包容我只要看到插座就像隻撲火的飛蛾並鼓勵我不要放棄，也很謝謝camp 期間帶我的老師、助教和同學們讓我能接觸這比賽並彼此共學；希望自己能向這些生命中重要的人們身上學習擇善固執與從善如流的能力，除了謝謝外，還是謝謝！

最後也想給自己一項完賽任務：重整過去30 天的文章，先想想自己會想調整哪些東西：哪些自我練習沒完成？哪些地方沒有很明白？哪些可持續深入研究？哪些可合併成一篇文章？

那就到這邊囉，2022 的比賽掰掰啦！爽哦 XD